import sys
import pymysql
import json
from typing import List, Dict, Any, Optional
from datetime import datetime
from PyQt5.QtWidgets import (QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout,
                             QLabel, QLineEdit, QPushButton, QTextEdit, QTabWidget, QGroupBox,
                             QMessageBox, QTableWidget, QTableWidgetItem, QHeaderView, QComboBox)
from PyQt5.QtCore import Qt
from PyQt5.QtGui import QFont


class KindergartenDBToolGUI(QMainWindow):
    def __init__(self):
        super().__init__()
        self.db_tool = KindergartenDBTool()
        self.init_ui()

    def init_ui(self):
        self.setWindowTitle('幼儿园管理系统工具集')
        self.setGeometry(100, 100, 1000, 700)

        # 中央部件
        central_widget = QWidget()
        self.setCentralWidget(central_widget)

        # 主布局
        main_layout = QVBoxLayout(central_widget)

        # 数据库连接组
        db_group = QGroupBox("数据库连接配置")
        db_layout = QHBoxLayout()

        self.host_input = QLineEdit("localhost")
        self.user_input = QLineEdit("root")
        self.password_input = QLineEdit("")
        self.password_input.setEchoMode(QLineEdit.Password)
        self.database_input = QLineEdit("kindergarten")
        self.port_input = QLineEdit("3306")

        db_layout.addWidget(QLabel("主机:"))
        db_layout.addWidget(self.host_input)
        db_layout.addWidget(QLabel("用户:"))
        db_layout.addWidget(self.user_input)
        db_layout.addWidget(QLabel("密码:"))
        db_layout.addWidget(self.password_input)
        db_layout.addWidget(QLabel("数据库:"))
        db_layout.addWidget(self.database_input)
        db_layout.addWidget(QLabel("端口:"))
        db_layout.addWidget(self.port_input)

        self.connect_btn = QPushButton("连接数据库")
        self.connect_btn.clicked.connect(self.connect_db)
        db_layout.addWidget(self.connect_btn)

        db_group.setLayout(db_layout)
        main_layout.addWidget(db_group)

        # 标签页
        self.tabs = QTabWidget()
        main_layout.addWidget(self.tabs)

        # 查询标签页
        self.setup_query_tab()

        # 修改标签页
        self.setup_modify_tab()

        # 删除标签页
        self.setup_delete_tab()

        # 微信管理标签页
        self.setup_wechat_tab()

        # 状态显示
        self.status_text = QTextEdit()
        self.status_text.setMaximumHeight(100)
        main_layout.addWidget(self.status_text)

        self.log("欢迎使用幼儿园管理系统工具集")

    def setup_query_tab(self):
        query_tab = QWidget()
        layout = QVBoxLayout(query_tab)

        # 手机号输入
        phone_layout = QHBoxLayout()
        phone_layout.addWidget(QLabel("家长手机号:"))
        self.query_phone_input = QLineEdit()
        phone_layout.addWidget(self.query_phone_input)

        self.query_btn = QPushButton("查询信息")
        self.query_btn.clicked.connect(self.query_info)
        phone_layout.addWidget(self.query_btn)

        layout.addLayout(phone_layout)

        # 结果显示区域
        result_layout = QHBoxLayout()

        # 家长信息
        parent_group = QGroupBox("家长信息")
        parent_layout = QVBoxLayout()
        self.parent_text = QTextEdit()
        self.parent_text.setMaximumHeight(150)
        parent_layout.addWidget(self.parent_text)
        parent_group.setLayout(parent_layout)
        result_layout.addWidget(parent_group)

        # 孩子信息
        children_group = QGroupBox("孩子信息")
        children_layout = QVBoxLayout()
        self.children_table = QTableWidget()
        self.children_table.setColumnCount(6)
        self.children_table.setHorizontalHeaderLabels(["姓名", "学号", "性别", "生日", "幼儿园", "班级"])
        children_layout.addWidget(self.children_table)
        children_group.setLayout(children_layout)
        result_layout.addWidget(children_group)

        layout.addLayout(result_layout)

        # 订单和服务信息
        service_layout = QHBoxLayout()

        # 订单信息
        orders_group = QGroupBox("订单信息")
        orders_layout = QVBoxLayout()
        self.orders_table = QTableWidget()
        self.orders_table.setColumnCount(7)
        self.orders_table.setHorizontalHeaderLabels(
            ["订单号", "标题", "幼儿园", "班级", "金额", "开始日期", "结束日期"])
        orders_layout.addWidget(self.orders_table)
        orders_group.setLayout(orders_layout)
        service_layout.addWidget(orders_group)

        # 视频服务信息
        video_group = QGroupBox("视频服务信息")
        video_layout = QVBoxLayout()
        self.video_table = QTableWidget()
        self.video_table.setColumnCount(6)
        self.video_table.setHorizontalHeaderLabels(["服务ID", "订单ID", "幼儿园", "班级", "开始日期", "结束日期"])
        video_layout.addWidget(self.video_table)
        video_group.setLayout(video_layout)
        service_layout.addWidget(video_group)

        layout.addLayout(service_layout)

        self.tabs.addTab(query_tab, "信息查询")

    def setup_modify_tab(self):
        modify_tab = QWidget()
        layout = QVBoxLayout(modify_tab)

        # 手机号输入
        phone_layout = QHBoxLayout()
        phone_layout.addWidget(QLabel("家长手机号:"))
        self.modify_phone_input = QLineEdit()
        phone_layout.addWidget(self.modify_phone_input)

        self.modify_query_btn = QPushButton("查询可修改项")
        self.modify_query_btn.clicked.connect(self.query_modifiable)
        phone_layout.addWidget(self.modify_query_btn)

        layout.addLayout(phone_layout)

        # 订单修改区域
        orders_group = QGroupBox("订单班级修改")
        orders_layout = QVBoxLayout()
        self.orders_modify_table = QTableWidget()
        self.orders_modify_table.setColumnCount(8)
        self.orders_modify_table.setHorizontalHeaderLabels(
            ["选择", "订单号", "标题", "幼儿园", "当前班级", "新班级", "开始日期", "结束日期"])
        orders_layout.addWidget(self.orders_modify_table)

        self.modify_orders_btn = QPushButton("修改选中订单的班级")
        self.modify_orders_btn.clicked.connect(self.modify_orders_classes)
        orders_layout.addWidget(self.modify_orders_btn)

        orders_group.setLayout(orders_layout)
        layout.addWidget(orders_group)

        # 视频服务修改区域
        video_group = QGroupBox("视频服务班级修改")
        video_layout = QVBoxLayout()
        self.video_modify_table = QTableWidget()
        self.video_modify_table.setColumnCount(8)
        self.video_modify_table.setHorizontalHeaderLabels(
            ["选择", "服务ID", "订单ID", "幼儿园", "当前班级", "新班级", "开始日期", "结束日期"])
        video_layout.addWidget(self.video_modify_table)

        self.modify_video_btn = QPushButton("修改选中服务的班级")
        self.modify_video_btn.clicked.connect(self.modify_video_classes)
        video_layout.addWidget(self.modify_video_btn)

        video_group.setLayout(video_layout)
        layout.addWidget(video_group)

        self.tabs.addTab(modify_tab, "班级修改")

    def setup_delete_tab(self):
        delete_tab = QWidget()
        layout = QVBoxLayout(delete_tab)

        # 手机号输入
        phone_layout = QHBoxLayout()
        phone_layout.addWidget(QLabel("家长手机号:"))
        self.delete_phone_input = QLineEdit()
        phone_layout.addWidget(self.delete_phone_input)

        # self.modify_query_btn = QPushButton("查询可删除项")
        # self.modify_query_btn.clicked.connect(self.query_modifiable)
        # phone_layout.addWidget(self.modify_query_btn)

        layout.addLayout(phone_layout)

        # 删除按钮区域
        buttons_layout = QHBoxLayout()

        self.delete_parent_btn = QPushButton("删除家长数据")
        self.delete_parent_btn.clicked.connect(self.delete_parent_data)
        buttons_layout.addWidget(self.delete_parent_btn)

        self.delete_orders_btn = QPushButton("删除订单数据")
        self.delete_orders_btn.clicked.connect(self.delete_orders_data)
        buttons_layout.addWidget(self.delete_orders_btn)

        layout.addLayout(buttons_layout)

        # 说明文本
        info_label = QLabel("警告: 删除操作不可逆，请谨慎操作!")
        info_label.setStyleSheet("color: red; font-weight: bold;")
        layout.addWidget(info_label)

        self.tabs.addTab(delete_tab, "数据删除")

    def setup_wechat_tab(self):
        wechat_tab = QWidget()
        layout = QVBoxLayout(wechat_tab)

        # 手机号输入
        phone_layout = QHBoxLayout()
        phone_layout.addWidget(QLabel("家长手机号:"))
        self.wechat_phone_input = QLineEdit()
        phone_layout.addWidget(self.wechat_phone_input)

        self.query_wechat_btn = QPushButton("查询微信信息")
        self.query_wechat_btn.clicked.connect(self.query_wechat_info)
        phone_layout.addWidget(self.query_wechat_btn)

        layout.addLayout(phone_layout)

        # 微信信息显示
        wechat_info_group = QGroupBox("微信登录信息")
        wechat_info_layout = QVBoxLayout()
        self.wechat_info_text = QTextEdit()
        self.wechat_info_text.setMaximumHeight(100)
        wechat_info_layout.addWidget(self.wechat_info_text)
        wechat_info_group.setLayout(wechat_info_layout)
        layout.addWidget(wechat_info_group)

        # 操作按钮
        self.clear_wechat_btn = QPushButton("清除微信登录")
        self.clear_wechat_btn.clicked.connect(self.clear_wechat_login)
        layout.addWidget(self.clear_wechat_btn)

        self.tabs.addTab(wechat_tab, "微信管理")

    def connect_db(self):
        try:
            host = self.host_input.text()
            user = self.user_input.text()
            password = self.password_input.text()
            database = self.database_input.text()
            port = int(self.port_input.text())

            self.db_tool.db_config.update({
                'host': host,
                'user': user,
                'password': password,
                'database': database,
                'port': port
            })

            if self.db_tool.connect():
                self.log("数据库连接成功")
                # 启用所有功能按钮
                self.enable_buttons(True)
            else:
                self.log("数据库连接失败")
        except Exception as e:
            self.log(f"连接数据库时出错: {str(e)}")

    def enable_buttons(self, enabled):
        self.query_btn.setEnabled(enabled)
        self.modify_query_btn.setEnabled(enabled)
        self.delete_parent_btn.setEnabled(enabled)
        self.delete_orders_btn.setEnabled(enabled)
        self.query_wechat_btn.setEnabled(enabled)
        self.clear_wechat_btn.setEnabled(enabled)

    def query_info(self):
        phone = self.query_phone_input.text()
        if not phone:
            QMessageBox.warning(self, "警告", "请输入手机号")
            return

        parent_info = self.db_tool.get_parent_by_phone(phone)
        if not parent_info:
            self.log(f"未找到手机号 {phone} 对应的家长信息")
            return

        # 显示家长信息
        parent_text = f"家长ID: {parent_info.get('parent_id')}\n"
        parent_text += f"姓名: {parent_info.get('parent_name')}\n"
        parent_text += f"手机号: {parent_info.get('parent_phone')}\n"
        parent_text += f"微信OpenID: {parent_info.get('mini_open_id')}\n"
        parent_text += f"孩子ID: {parent_info.get('child_id')}"
        self.parent_text.setText(parent_text)

        # 查询并显示孩子信息
        children = self.db_tool.get_children_by_parent_id(parent_info['parent_id'])
        self.display_children_table(children)

        # 获取孩子当前所在幼儿园的ID列表
        kindergarten_ids = list(set([child['kindergarten_id'] for child in children]))

        # 查询并显示订单信息
        orders = self.db_tool.get_orders_by_parent_id(parent_info['parent_id'], kindergarten_ids)
        self.display_orders_table(orders)

        # 查询并显示视频服务信息
        video_services = self.db_tool.get_video_services_by_parent_id(parent_info['parent_id'], kindergarten_ids)
        self.display_video_table(video_services)

        self.log(f"已查询手机号 {phone} 的信息")

    def display_children_table(self, children):
        self.children_table.setRowCount(len(children))
        for row, child in enumerate(children):
            # 获取班级和幼儿园名称
            classes_info = self.db_tool.get_classes_by_id(child.get('classes_id'))
            kindergarten_info = self.db_tool.get_kindergarten_by_id(child.get('kindergarten_id'))

            classes_name = classes_info.get('classes_name') if classes_info else "未知班级"
            kindergarten_name = kindergarten_info.get('kindergarten_name') if kindergarten_info else "未知幼儿园"

            self.children_table.setItem(row, 0, QTableWidgetItem(child.get('child_name', '')))
            self.children_table.setItem(row, 1, QTableWidgetItem(child.get('child_number', '')))
            self.children_table.setItem(row, 2, QTableWidgetItem('男' if child.get('child_sex') == 1 else '女'))
            self.children_table.setItem(row, 3, QTableWidgetItem(str(child.get('child_birthday', ''))))
            self.children_table.setItem(row, 4, QTableWidgetItem(kindergarten_name))
            self.children_table.setItem(row, 5, QTableWidgetItem(classes_name))

        self.children_table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)

    def display_orders_table(self, orders):
        self.orders_table.setRowCount(len(orders))
        for row, order in enumerate(orders):
            # 获取班级和幼儿园名称
            classes_info = self.db_tool.get_classes_by_id(order.get('classes_id'))
            kindergarten_info = self.db_tool.get_kindergarten_by_id(order.get('kindergarten_id'))

            classes_name = classes_info.get('classes_name') if classes_info else "未知班级"
            kindergarten_name = kindergarten_info.get('kindergarten_name') if kindergarten_info else "未知幼儿园"

            self.orders_table.setItem(row, 0, QTableWidgetItem(order.get('order_no', '')))
            self.orders_table.setItem(row, 1, QTableWidgetItem(order.get('order_title', '')))
            self.orders_table.setItem(row, 2, QTableWidgetItem(kindergarten_name))
            self.orders_table.setItem(row, 3, QTableWidgetItem(classes_name))
            self.orders_table.setItem(row, 4, QTableWidgetItem(str(order.get('order_amount', ''))))
            self.orders_table.setItem(row, 5, QTableWidgetItem(str(order.get('start_date', ''))))
            self.orders_table.setItem(row, 6, QTableWidgetItem(str(order.get('end_date', ''))))

        self.orders_table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)

    def display_video_table(self, video_services):
        self.video_table.setRowCount(len(video_services))
        for row, service in enumerate(video_services):
            # 获取班级和幼儿园名称
            classes_info = self.db_tool.get_classes_by_id(service.get('classes_id'))
            kindergarten_info = self.db_tool.get_kindergarten_by_id(service.get('kindergarten_id'))

            classes_name = classes_info.get('classes_name') if classes_info else "未知班级"
            kindergarten_name = kindergarten_info.get('kindergarten_name') if kindergarten_info else "未知幼儿园"

            self.video_table.setItem(row, 0, QTableWidgetItem(service.get('video_service_parent_id', '')))
            self.video_table.setItem(row, 1, QTableWidgetItem(service.get('service_order_id', '')))
            self.video_table.setItem(row, 2, QTableWidgetItem(kindergarten_name))
            self.video_table.setItem(row, 3, QTableWidgetItem(classes_name))
            self.video_table.setItem(row, 4, QTableWidgetItem(str(service.get('start_date', ''))))
            self.video_table.setItem(row, 5, QTableWidgetItem(str(service.get('end_date', ''))))

        self.video_table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)

    def query_modifiable(self):
        phone = self.modify_phone_input.text()
        if not phone:
            QMessageBox.warning(self, "警告", "请输入手机号")
            return

        parent_info = self.db_tool.get_parent_by_phone(phone)
        if not parent_info:
            self.log(f"未找到手机号 {phone} 对应的家长信息")
            return

        # 查询孩子信息
        children = self.db_tool.get_children_by_parent_id(parent_info['parent_id'])

        # 获取孩子当前所在幼儿园的ID列表
        kindergarten_ids = list(set([child['kindergarten_id'] for child in children]))

        # 查询订单信息
        orders = self.db_tool.get_orders_by_parent_id(parent_info['parent_id'], kindergarten_ids)
        self.display_orders_modify_table(orders)

        # 查询视频服务信息
        video_services = self.db_tool.get_video_services_by_parent_id(parent_info['parent_id'], kindergarten_ids)
        self.display_video_modify_table(video_services)

        self.log(f"已查询手机号 {phone} 的可修改项")

    def display_orders_modify_table(self, orders):
        self.orders_modify_table.setRowCount(len(orders))
        for row, order in enumerate(orders):
            # 获取班级和幼儿园名称
            classes_info = self.db_tool.get_classes_by_id(order.get('classes_id'))
            kindergarten_info = self.db_tool.get_kindergarten_by_id(order.get('kindergarten_id'))

            classes_name = classes_info.get('classes_name') if classes_info else "未知班级"
            kindergarten_name = kindergarten_info.get('kindergarten_name') if kindergarten_info else "未知幼儿园"

            # 选择复选框
            checkbox = QTableWidgetItem()
            checkbox.setCheckState(Qt.Unchecked)
            self.orders_modify_table.setItem(row, 0, checkbox)

            self.orders_modify_table.setItem(row, 1, QTableWidgetItem(order.get('order_no', '')))
            self.orders_modify_table.setItem(row, 2, QTableWidgetItem(order.get('order_title', '')))
            self.orders_modify_table.setItem(row, 3, QTableWidgetItem(kindergarten_name))
            self.orders_modify_table.setItem(row, 4, QTableWidgetItem(classes_name))

            # 新班级选择框
            classes_combo = QComboBox()
            classes_list = self.db_tool.get_classes_by_kindergarten(order.get('kindergarten_id'))
            for class_info in classes_list:
                classes_combo.addItem(f"{class_info.get('classes_name')} ({class_info.get('classes_id')})",
                                      class_info.get('classes_id'))
            self.orders_modify_table.setCellWidget(row, 5, classes_combo)

            self.orders_modify_table.setItem(row, 6, QTableWidgetItem(str(order.get('start_date', ''))))
            self.orders_modify_table.setItem(row, 7, QTableWidgetItem(str(order.get('end_date', ''))))

        self.orders_modify_table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)

    def display_video_modify_table(self, video_services):
        self.video_modify_table.setRowCount(len(video_services))
        for row, service in enumerate(video_services):
            # 获取班级和幼儿园名称
            classes_info = self.db_tool.get_classes_by_id(service.get('classes_id'))
            kindergarten_info = self.db_tool.get_kindergarten_by_id(service.get('kindergarten_id'))

            classes_name = classes_info.get('classes_name') if classes_info else "未知班级"
            kindergarten_name = kindergarten_info.get('kindergarten_name') if kindergarten_info else "未知幼儿园"

            # 选择复选框
            checkbox = QTableWidgetItem()
            checkbox.setCheckState(Qt.Unchecked)
            self.video_modify_table.setItem(row, 0, checkbox)

            self.video_modify_table.setItem(row, 1, QTableWidgetItem(service.get('video_service_parent_id', '')))
            self.video_modify_table.setItem(row, 2, QTableWidgetItem(service.get('service_order_id', '')))
            self.video_modify_table.setItem(row, 3, QTableWidgetItem(kindergarten_name))
            self.video_modify_table.setItem(row, 4, QTableWidgetItem(classes_name))

            # 新班级选择框
            classes_combo = QComboBox()
            classes_list = self.db_tool.get_classes_by_kindergarten(service.get('kindergarten_id'))
            for class_info in classes_list:
                classes_combo.addItem(f"{class_info.get('classes_name')} ({class_info.get('classes_id')})",
                                      class_info.get('classes_id'))
            self.video_modify_table.setCellWidget(row, 5, classes_combo)

            self.video_modify_table.setItem(row, 6, QTableWidgetItem(str(service.get('start_date', ''))))
            self.video_modify_table.setItem(row, 7, QTableWidgetItem(str(service.get('end_date', ''))))

        self.video_modify_table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)

    def modify_orders_classes(self):
        modified_count = 0
        for row in range(self.orders_modify_table.rowCount()):
            if self.orders_modify_table.item(row, 0).checkState() == Qt.Checked:
                order_no = self.orders_modify_table.item(row, 1).text()
                classes_combo = self.orders_modify_table.cellWidget(row, 5)
                new_classes_id = classes_combo.currentData()

                # 找到对应的订单
                phone = self.modify_phone_input.text()
                parent_info = self.db_tool.get_parent_by_phone(phone)
                if not parent_info:
                    continue

                children = self.db_tool.get_children_by_parent_id(parent_info['parent_id'])
                kindergarten_ids = list(set([child['kindergarten_id'] for child in children]))
                orders = self.db_tool.get_orders_by_parent_id(parent_info['parent_id'], kindergarten_ids)

                target_order = None
                for order in orders:
                    if order.get('order_no') == order_no:
                        target_order = order
                        break

                if target_order and self.db_tool.update_order_classes_id(
                        target_order['service_order_id'], new_classes_id, target_order['child_ids']):
                    modified_count += 1

        self.log(f"已修改 {modified_count} 个订单的班级")
        QMessageBox.information(self, "完成", f"已修改 {modified_count} 个订单的班级")

    def modify_video_classes(self):
        modified_count = 0
        for row in range(self.video_modify_table.rowCount()):
            if self.video_modify_table.item(row, 0).checkState() == Qt.Checked:
                service_id = self.video_modify_table.item(row, 1).text()
                classes_combo = self.video_modify_table.cellWidget(row, 5)
                new_classes_id = classes_combo.currentData()

                # 找到对应的服务
                phone = self.modify_phone_input.text()
                parent_info = self.db_tool.get_parent_by_phone(phone)
                if not parent_info:
                    continue

                children = self.db_tool.get_children_by_parent_id(parent_info['parent_id'])
                kindergarten_ids = list(set([child['kindergarten_id'] for child in children]))
                video_services = self.db_tool.get_video_services_by_parent_id(parent_info['parent_id'],
                                                                              kindergarten_ids)

                target_service = None
                for service in video_services:
                    if service.get('video_service_parent_id') == service_id:
                        target_service = service
                        break

                if target_service and self.db_tool.update_video_service_classes_id(
                        target_service['video_service_parent_id'], new_classes_id, target_service['child_ids']):
                    modified_count += 1

        self.log(f"已修改 {modified_count} 个视频服务的班级")
        QMessageBox.information(self, "完成", f"已修改 {modified_count} 个视频服务的班级")

    def delete_parent_data(self):
        phone = self.delete_phone_input.text()
        if not phone:
            QMessageBox.warning(self, "警告", "请输入手机号")
            return

        reply = QMessageBox.question(self, "确认",
                                     f"确定要删除手机号 {phone} 的所有家长和孩子数据吗？此操作不可恢复！",
                                     QMessageBox.Yes | QMessageBox.No)

        if reply == QMessageBox.Yes:
            if self.db_tool.delete_parent_data(phone):
                QMessageBox.information(self, "成功", "删除家长数据成功")
                self.log(f"已删除手机号 {phone} 的家长数据")
            else:
                QMessageBox.warning(self, "失败", "删除家长数据失败")

    def delete_orders_data(self):
        phone = self.delete_phone_input.text()
        if not phone:
            QMessageBox.warning(self, "警告", "请输入手机号")
            return

        reply = QMessageBox.question(self, "确认",
                                     f"确定要删除手机号 {phone} 的所有订单数据吗？此操作不可恢复！",
                                     QMessageBox.Yes | QMessageBox.No)

        if reply == QMessageBox.Yes:
            if self.db_tool.delete_order_data(phone):
                QMessageBox.information(self, "成功", "删除订单数据成功")
                self.log(f"已删除手机号 {phone} 的订单数据")
            else:
                QMessageBox.warning(self, "失败", "删除订单数据失败")

    def query_wechat_info(self):
        phone = self.wechat_phone_input.text()
        if not phone:
            QMessageBox.warning(self, "警告", "请输入手机号")
            return

        parent_info = self.db_tool.get_parent_by_phone(phone)
        if not parent_info:
            self.log(f"未找到手机号 {phone} 对应的家长信息")
            return

        wechat_text = f"家长: {parent_info.get('parent_name')}\n"
        wechat_text += f"手机号: {parent_info.get('parent_phone')}\n"
        wechat_text += f"微信OpenID: {parent_info.get('mini_open_id') or '未绑定'}\n"
        wechat_text += f"公众号OpenID: {parent_info.get('official_open_id') or '未绑定'}\n"
        wechat_text += f"UnionID: {parent_info.get('wechat_union_id') or '未绑定'}"

        self.wechat_info_text.setText(wechat_text)
        self.log(f"已查询手机号 {phone} 的微信信息")

    def clear_wechat_login(self):
        phone = self.wechat_phone_input.text()
        if not phone:
            QMessageBox.warning(self, "警告", "请输入手机号")
            return

        reply = QMessageBox.question(self, "确认",
                                     f"确定要清除手机号 {phone} 的微信登录信息吗？",
                                     QMessageBox.Yes | QMessageBox.No)

        if reply == QMessageBox.Yes:
            if self.db_tool.clear_wechat_login(phone):
                QMessageBox.information(self, "成功", "清除微信登录成功")
                self.log(f"已清除手机号 {phone} 的微信登录信息")
                # 刷新微信信息显示
                self.query_wechat_info()
            else:
                QMessageBox.warning(self, "失败", "清除微信登录失败")

    def log(self, message):
        timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        self.status_text.append(f"[{timestamp}] {message}")

    def closeEvent(self, event):
        self.db_tool.disconnect()
        event.accept()


# 保留原有的KindergartenDBTool类，但需要稍作修改以适配GUI
class KindergartenDBTool:
    def __init__(self):
        self.db_config = {
            'host': 'localhost',
            'user': 'root',
            'password': '',
            'database': 'kindergarten',
            'port': 3306,
            'charset': 'utf8mb4',
            'cursorclass': pymysql.cursors.DictCursor
        }
        self.connection = None

    def connect(self):
        """连接到数据库"""
        try:
            self.connection = pymysql.connect(**self.db_config)
            return True
        except Exception as e:
            print(f"数据库连接失败: {e}")
            return False

    def disconnect(self):
        """断开数据库连接"""
        if self.connection:
            self.connection.close()

    def get_parent_by_phone(self, phone: str) -> Optional[Dict[str, Any]]:
        """根据手机号查询家长信息"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_parent 
                    WHERE parent_phone = %s AND is_delete = 0
                """
                cursor.execute(sql, (phone,))
                result = cursor.fetchone()
                return result
        except Exception as e:
            print(f"查询家长信息失败: {e}")
            return None

    def get_children_by_parent_id(self, parent_id: str) -> List[Dict[str, Any]]:
        """根据家长ID查询孩子信息，增加child_status=1的条件"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_child 
                    WHERE parent_id = %s 
                      AND is_delete = 0
                      AND child_status = 1
                """
                cursor.execute(sql, (parent_id,))
                results = cursor.fetchall()
                return results
        except Exception as e:
            print(f"查询孩子信息失败: {e}")
            return []

    def get_kindergarten_by_id(self, kindergarten_id: str) -> Optional[Dict[str, Any]]:
        """根据幼儿园ID查询幼儿园信息"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_kindergarten 
                    WHERE kindergarten_id = %s AND is_delete = 0
                """
                cursor.execute(sql, (kindergarten_id,))
                result = cursor.fetchone()
                return result
        except Exception as e:
            print(f"查询幼儿园信息失败: {e}")
            return None

    def get_classes_by_id(self, classes_id: str) -> Optional[Dict[str, Any]]:
        """根据班级ID查询班级信息"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_classes 
                    WHERE classes_id = %s AND is_delete = 0
                """
                cursor.execute(sql, (classes_id,))
                result = cursor.fetchone()
                return result
        except Exception as e:
            print(f"查询班级信息失败: {e}")
            return None

    def get_classes_by_kindergarten(self, kindergarten_id: str) -> List[Dict[str, Any]]:
        """根据幼儿园ID查询所有班级信息"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_classes 
                    WHERE kindergarten_id = %s AND is_delete = 0
                    ORDER BY classes_name
                """
                cursor.execute(sql, (kindergarten_id,))
                results = cursor.fetchall()
                return results
        except Exception as e:
            print(f"查询班级列表失败: {e}")
            return []

    def get_orders_by_parent_id(self, parent_id: str, kindergarten_ids: List[str]) -> List[Dict[str, Any]]:
        """根据家长ID查询订单信息，增加end_date大于当前日期的限制，并且只显示孩子当前所在幼儿园的数据"""
        try:
            if not kindergarten_ids:
                return []

            with self.connection.cursor() as cursor:
                # 构建IN条件的占位符
                placeholders = ','.join(['%s'] * len(kindergarten_ids))
                sql = f"""
                    SELECT * FROM kind_tb_parent_service_order 
                    WHERE parent_id = %s 
                      AND order_status = 2 
                      AND is_delete = 0
                      AND (end_date IS NULL OR end_date > CURDATE())
                      AND kindergarten_id IN ({placeholders})
                """
                # 构建参数列表
                params = [parent_id] + kindergarten_ids
                cursor.execute(sql, params)
                results = cursor.fetchall()
                return results
        except Exception as e:
            print(f"查询订单信息失败: {e}")
            return []

    def get_video_services_by_parent_id(self, parent_id: str, kindergarten_ids: List[str]) -> List[Dict[str, Any]]:
        """根据家长ID查询视频服务信息，增加end_date大于当前日期的限制，并且只显示孩子当前所在幼儿园的数据"""
        try:
            if not kindergarten_ids:
                return []

            with self.connection.cursor() as cursor:
                # 构建IN条件的占位符
                placeholders = ','.join(['%s'] * len(kindergarten_ids))
                sql = f"""
                    SELECT * FROM kind_tb_video_service_parent 
                    WHERE parent_id = %s 
                      AND is_delete = 0
                      AND (end_date IS NULL OR end_date > CURDATE())
                      AND kindergarten_id IN ({placeholders})
                """
                # 构建参数列表
                params = [parent_id] + kindergarten_ids
                cursor.execute(sql, params)
                results = cursor.fetchall()
                return results
        except Exception as e:
            print(f"查询视频服务信息失败: {e}")
            return []

    def update_order_classes_id(self, order_id: str, new_classes_id: str, child_ids_json: str) -> bool:
        """更新订单表中的classes_id，并同时更新kind_tb_child表中的classes_id"""
        try:
            with self.connection.cursor() as cursor:
                # 解析child_ids JSON
                try:
                    child_ids = json.loads(child_ids_json)
                    if not isinstance(child_ids, list):
                        print(f"订单 {order_id} 的child_ids格式错误")
                        return False
                except json.JSONDecodeError:
                    print(f"订单 {order_id} 的child_ids JSON解析失败")
                    return False

                # 更新订单表的classes_id
                sql_update_order = """
                    UPDATE kind_tb_parent_service_order 
                    SET classes_id = %s, update_time = NOW() 
                    WHERE service_order_id = %s
                """
                cursor.execute(sql_update_order, (new_classes_id, order_id))

                # 更新孩子表的classes_id
                if child_ids:
                    # 构建IN条件的占位符
                    placeholders = ','.join(['%s'] * len(child_ids))
                    sql_update_child = f"""
                        UPDATE kind_tb_child 
                        SET classes_id = %s, update_time = NOW() 
                        WHERE child_id IN ({placeholders})
                    """
                    # 参数：新班级ID + 孩子ID列表
                    params = [new_classes_id] + child_ids
                    cursor.execute(sql_update_child, params)

                self.connection.commit()
                return True
        except Exception as e:
            print(f"更新订单classes_id失败: {e}")
            self.connection.rollback()
            return False

    def update_video_service_classes_id(self, video_service_id: str, new_classes_id: str, child_ids_json: str) -> bool:
        """更新视频服务表中的classes_id，并同时更新kind_tb_child表中的classes_id"""
        try:
            with self.connection.cursor() as cursor:
                # 解析child_ids JSON
                try:
                    child_ids = json.loads(child_ids_json)
                    if not isinstance(child_ids, list):
                        print(f"视频服务 {video_service_id} 的child_ids格式错误")
                        return False
                except json.JSONDecodeError:
                    print(f"视频服务 {video_service_id} 的child_ids JSON解析失败")
                    return False

                # 更新视频服务表的classes_id
                sql_update_service = """
                    UPDATE kind_tb_video_service_parent 
                    SET classes_id = %s, update_time = NOW() 
                    WHERE video_service_parent_id = %s
                """
                cursor.execute(sql_update_service, (new_classes_id, video_service_id))

                # 更新孩子表的classes_id
                if child_ids:
                    # 构建IN条件的占位符
                    placeholders = ','.join(['%s'] * len(child_ids))
                    sql_update_child = f"""
                        UPDATE kind_tb_child 
                        SET classes_id = %s, update_time = NOW() 
                        WHERE child_id IN ({placeholders})
                    """
                    # 参数：新班级ID + 孩子ID列表
                    params = [new_classes_id] + child_ids
                    cursor.execute(sql_update_child, params)

                self.connection.commit()
                return True
        except Exception as e:
            print(f"更新视频服务classes_id失败: {e}")
            self.connection.rollback()
            return False

    def delete_parent_data(self, phone: str) -> bool:
        """删除家长用户数据"""
        try:
            with self.connection.cursor() as cursor:
                # 查询家长信息
                parent_info = self.get_parent_by_phone(phone)
                if not parent_info:
                    print(f"未找到手机号 {phone} 对应的家长信息")
                    return False

                parent_id = parent_info['parent_id']
                child_id = parent_info.get('child_id')

                # 删除kind_tb_parent中的数据
                sql_delete_parent = """
                    DELETE FROM kind_tb_parent 
                    WHERE parent_id = %s
                """
                cursor.execute(sql_delete_parent, (parent_id,))

                # 如果有child_id，删除kind_tb_child中的数据
                if child_id:
                    sql_delete_child = """
                        DELETE FROM kind_tb_child 
                        WHERE child_id = %s
                    """
                    cursor.execute(sql_delete_child, (child_id,))

                self.connection.commit()
                return True
        except Exception as e:
            print(f"删除家长数据失败: {e}")
            self.connection.rollback()
            return False

    def delete_order_data(self, phone: str) -> bool:
        """删除订单记录数据"""
        try:
            with self.connection.cursor() as cursor:
                # 查询家长信息
                parent_info = self.get_parent_by_phone(phone)
                if not parent_info:
                    print(f"未找到手机号 {phone} 对应的家长信息")
                    return False

                parent_id = parent_info['parent_id']

                # 删除kind_tb_parent_service_order中的数据
                sql_delete_orders = """
                    DELETE FROM kind_tb_parent_service_order 
                    WHERE parent_id = %s
                """
                cursor.execute(sql_delete_orders, (parent_id,))

                # 删除kind_tb_video_service_parent中的数据
                sql_delete_video_services = """
                    DELETE FROM kind_tb_video_service_parent 
                    WHERE parent_id = %s
                """
                cursor.execute(sql_delete_video_services, (parent_id,))

                self.connection.commit()
                return True
        except Exception as e:
            print(f"删除订单数据失败: {e}")
            self.connection.rollback()
            return False

    def clear_wechat_login(self, phone: str) -> bool:
        """清除用户登录微信"""
        try:
            with self.connection.cursor() as cursor:
                # 查询家长信息
                parent_info = self.get_parent_by_phone(phone)
                if not parent_info:
                    print(f"未找到手机号 {phone} 对应的家长信息")
                    return False

                parent_id = parent_info['parent_id']

                # 清空mini_open_id字段
                sql_clear_wechat = """
                    UPDATE kind_tb_parent 
                    SET mini_open_id = NULL, update_time = NOW() 
                    WHERE parent_id = %s
                """
                cursor.execute(sql_clear_wechat, (parent_id,))

                self.connection.commit()
                return True
        except Exception as e:
            print(f"清除微信登录信息失败: {e}")
            self.connection.rollback()
            return False


if __name__ == "__main__":
    app = QApplication(sys.argv)
    window = KindergartenDBToolGUI()
    window.show()
    sys.exit(app.exec_())